package com.letv.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.PropertyConfigurator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.letv.bean.CpInfoBean;
import com.letv.bean.SourceLogoBean;
import com.letv.bean.ThumbnailBean;
import com.letv.common.ConnectionManager;
import com.letv.common.Constants;
import com.letv.common.Mappings;
import com.letv.conf.ConfigFactory;
import com.letv.mq.addMms.MqAddBean.Type;

public class SQLUtil {

    private final static Logger log = LoggerFactory.getLogger(SQLUtil.class);
	
    private static List<HashMap<String, Object>> SOURCE_SORT;

    static {
        SOURCE_SORT = new ArrayList<HashMap<String, Object>>();
        HashMap<String, Object> top = new HashMap<String, Object>();
        top.put("id", 51484);
        top.put("domain", "letv");
        SOURCE_SORT.add(top);
    }
    

    @SuppressWarnings("finally")
    public static String getPnameByPid(String pid) {
        String panme = "";
        if (StringUtils.isBlank(pid)){
            return panme;
        }
        String[] ids = pid.split("\\,");
        
        String para = "";
        for (String id: ids) {
            if (StringUtils.isNotBlank(id)) {
                para +=id+",";
            }
        }
        if (para.endsWith(",")) {
            para = para.substring(0, para.length()-1);
        }
        ConnectionManager cm = null;
        Connection connToMMS =null;
        try {
            cm = ConnectionManager.getInstance();
            connToMMS = cm.getConnectionFromMMS();
            String selSql = "SELECT GROUP_CONCAT(VALUE) pname FROM mms.`db_dictionary_info` WHERE id IN ("+para+")";
            Statement stmt = connToMMS.createStatement();
            ResultSet rst = stmt.executeQuery(selSql);
            while (rst.next()) {
                panme = rst.getString("pname");
            }
            close(rst);
            close(stmt);
        } catch (Exception e) {
            log.error("get professional name error!",e);
        } finally {
            close(connToMMS);
            return panme;
        }
    }

	public static void setDictionary(Connection conn) throws SQLException {
		String selSql = ConfigFactory.getString("common.getDictionary");

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		while (rst.next()) {
            Map<String, String> values = new HashMap<String, String>();
            values.put("value", String.valueOf(rst.getObject(2)));
            values.put("value_en", String.valueOf(rst.getObject(3)));
            Mappings.DICTIONARY_MAP.put(String.valueOf(rst.getObject(1)), values);
		}

		close(rst);
		close(stmt);
	}

	public static void setStar(Connection conn) throws SQLException {
		String selSql = ConfigFactory.getString("common.getStar");

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		while (rst.next()) {
			//@todo Mappings.STAR_MAP.put(String.valueOf(rst.getObject(1)), String.valueOf(rst.getObject(2))+"@@"+String.valueOf(rst.getObject(3)));
			Mappings.STAR_MAP.put(String.valueOf(rst.getObject(1)), String.valueOf(rst.getObject(2)));
			Mappings.STAR_PIC_MAP.put(String.valueOf(rst.getObject(1)), String.valueOf(rst.getObject(3)));
		}

		close(rst);
		close(stmt);
	}

	public static void setRAlbumMap(Connection conn) throws SQLException {
		String selSql = "select RELATION_ID from mms.con_relation_info ";

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		while (rst.next()) {
			String relationIds = String.valueOf(rst.getObject(1));
			if(!StringUtils.isBlank(relationIds)){
				String[] ids = relationIds.split(",");
				for(String id:ids){
					Mappings.RALBUM_MAP.put(id,relationIds);
				}
			}
		}

		close(rst);
		close(stmt);
	}

	public static String getSingleSql(String table, String id) {
		return "SELECT * FROM " + table + " WHERE ID = " + id;
	}

	public static String getBatchSql(String table, int start, int limit) {
		return "SELECT * FROM " + table + " LIMIT " + start + ", " + limit;
	}

	public static int getCount(Connection conn, String table) throws SQLException {
		String selSql = ConfigFactory.getString("common.getCount") + " " + table;

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		rst.next();
		int count = rst.getInt(1);

		close(rst);
		close(stmt);

		return count;
	}

	public static ArrayList<String> getIdRangeNewLeso(Connection conn) throws SQLException {
		ArrayList<String> record = new ArrayList<String>();
		String selSql = "SELECT MIN(ID) minId, MAX(ID) maxId FROM album";

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		rst.next();
		record.add(0, rst.getObject("minId").toString());
		record.add(1, rst.getObject("maxId").toString());

		close(rst);
		close(stmt);

		return record;
	}

	public static ArrayList<String> getIdRangeNewLesoWebsite(Connection conn) throws SQLException {
		ArrayList<String> record = new ArrayList<String>();
        String selSql = "SELECT MIN(ID) minId, MAX(ID) maxId FROM video";

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		rst.next();
		record.add(0, rst.getObject("minId").toString());
		record.add(1, rst.getObject("maxId").toString());

		close(rst);
		close(stmt);

		return record;
	}

	/**
     * get the range of cp_info id from database
     * If there is no mistake,the size of the result list is 2.index 0 is the min id,index 1 is the max id
	 * @param connectionFromLeso Connection, connection to database
     * @param tableName String, table name in database
	 * @return List<String>
	 * @throws Exception
	 */
    public static List<String> getIdRangeFromTableName(Connection connectionFromLeso, String tableName) throws Exception {
        if(null == connectionFromLeso || StringUtils.isBlank(tableName)) {
            return null;
        }
        List<String> result = new ArrayList<String>();
        String sql = "SELECT MIN(id) minId, MAX(id) maxId FROM " + tableName;
        Statement statement = connectionFromLeso.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        resultSet.next();
        result.add(0, resultSet.getObject("minId").toString());
        result.add(1, resultSet.getObject("maxId").toString());
        close(resultSet);
        close(statement);
        return result;
    }

	public static ArrayList<String> getIdRange(Connection conn, String table, String whereClause) throws SQLException {
		ArrayList<String> record = new ArrayList<String>();
		String selSql = ConfigFactory.getString("common.getIdRange") + " " + table + (whereClause == null ? "" : whereClause);
		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		rst.next();
		record.add(0, rst.getObject("minId").toString());
		record.add(1, rst.getObject("maxId").toString());

		close(rst);
		close(stmt);

		return record;
	}

	public static HashMap<String, Object> getDataById(Connection conn, String table, Long id) throws SQLException {
		HashMap<String, Object> record = new HashMap<String, Object>();
		String selSql = "SELECT * FROM " + table + " WHERE ID = ?";

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setLong(1, id);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		if (rst.next()) {
			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}

			close(rst);
			close(pstmt);

			return record;
		} else {
        	close(rst);
    		close(pstmt);

        	return null;
		}
	}

	public static ArrayList<String> getSourceDataIdRange(Connection conn, String type) throws SQLException {
		ArrayList<String> record = new ArrayList<String>();
		String selSql = ConfigFactory.getString(type + ".getSource" + StringUtil.toFirstLetterUpperCase(type) + "IdRange");

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);

		rst.next();
		record.add(0, rst.getObject("minId").toString());
		record.add(1, rst.getObject("maxId").toString());

		close(rst);
		close(stmt);

		return record;
	}

	public static ArrayList<Map<String, Object>> getLesoAlbumVideoSourceData(Connection conn, String type, String...params) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = ConfigFactory.getString(type);
		PreparedStatement pstmt = conn.prepareStatement(selSql);
		for (int i=1;i<=params.length;i++) {
			pstmt.setString(i, params[i-1]);
		}

		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			records.add(record);
		}

		close(rst);
		close(pstmt);

		return records;
	}

	public static ArrayList<Map<String, Object>> getLesoAlbumSourceData(Connection conn, String type, Long start, Long end) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = ConfigFactory.getString(type);

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setLong(1, start);
		pstmt.setLong(2, end);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();
		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			record.put("h5_blocked_city_level", SQLUtil.getH5BlockByAlbumId(conn, record.get("aid").toString()));
			records.add(record);
		}
		close(rst);
		close(pstmt);

		return records;
	}

	public static ArrayList<Map<String, Object>> getSourceData(Connection conn, String type, Long start, Long end) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = ConfigFactory.getString(type + ".getSource" + StringUtil.toFirstLetterUpperCase(type) + "Data");

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setLong(1, start);
		pstmt.setLong(2, end);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			records.add(record);
		}

		close(rst);
		close(pstmt);

		return records;
	}

	/**
	 * 获取默认站点源详情
	 * @param conn Connection
	 * @param type String
	 * @param id Long
	 * @return HashMap<String, Object>
	 * @throws SQLException
	 */
	public static HashMap<String, Object> getSourceDataById(Connection conn, String type, Long id) throws SQLException {
	    boolean ifLesoOrWebsite = "leso".equals(type) || "lesoExtraet".equals(type);
		ArrayList<HashMap<String, Object>> records = new ArrayList<HashMap<String, Object>>();
		String selSql = ConfigFactory.getString(type + ".getSource" + StringUtil.toFirstLetterUpperCase(type) + "DataById");
		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setLong(1, id);
		ResultSet rst = pstmt.executeQuery();
		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();
        while (rst.next()) {
            HashMap<String, Object> temp = new HashMap<String, Object>();
            
            for (int i = 1; i <= colCount; i++) {
                String colName = rsmd.getColumnLabel(i);
                temp.put(colName, rst.getObject(i));
            }
            records.add(temp);
        }
        close(rst);
        close(pstmt);
        if(null == records || records.size() == 0) {
            return null;
        }
        if(!ifLesoOrWebsite) {
            return records.get(0);
        }
        List<HashMap<String, Object>> defaultVideoList = SQLUtil.getDefaultVideoList(records);
        List<HashMap<String, Object>> tvVideoList = SQLUtil.getTvVideoList(records);
        SQLUtil.sortVideo(conn, defaultVideoList, tvVideoList, id);
        HashMap<String, Object> defaultVideo = SQLUtil.merageDefaultVideo(defaultVideoList, tvVideoList);
        if(null == defaultVideo) {
            return null;
        }
        defaultVideo.put("cp_letv_original_id", SQLUtil.getLOIDByCpId(conn, records.get(0).get("cp_info_id")));
        return defaultVideo;
	}

	private static void sortVideo(Connection conn, List<HashMap<String, Object>> defaultVideoList, List<HashMap<String, Object>> tvVideoList, Long aid) {
        if(null != defaultVideoList) {
            for(HashMap<String, Object> defaultVideo : defaultVideoList) {
                SQLUtil.setTempDefSort(conn, defaultVideo, aid);
            }
            Collections.sort(defaultVideoList, new SortByDefSort());
        }
        if(null != tvVideoList) {
            for(HashMap<String, Object> tvVideo : tvVideoList) {
                SQLUtil.setTempTvSort(tvVideo);
            }
            Collections.sort(tvVideoList, new SortByTvSort());
        }
    }

    /**
	 * 在tv索引及非tv索引中选出通用默认源
	 * @param defaultVideoList List<HashMap<String, Object>>, 非tv排序后的数据
	 * @param tvVideoList List<HashMap<String, Object>>, tv排序后的数据
	 * @return HashMap<String, Object>, 通用默认源
	 */
	private static HashMap<String, Object> merageDefaultVideo(List<HashMap<String, Object>> defaultVideoList, List<HashMap<String, Object>> tvVideoList) {
	    HashMap<String, Object> result = null;
	    if(null != defaultVideoList && defaultVideoList.size() > 0) {
	        result = defaultVideoList.get(0);
	    } else if(null != tvVideoList && tvVideoList.size() > 0) {
            result = tvVideoList.get(0);
        } else {
            return null;
        }
	    Map<String, String> source_list = new HashMap<String, String>();
	    source_list.put("default", SQLUtil.getSourceList(defaultVideoList));
	    source_list.put(Constants.PUSHFLAG_TV, SQLUtil.getSourceList(tvVideoList)); // push_flag=420007&cibn过审
	    result.put("source_list", source_list);
	    result.put("leso_def_web_set", 0);
	    result.put("if_default_source", 1);
        return result;
    }

	/**
	 * 生成站点源排序字符串
	 * @param videoList List<HashMap<String, Object>>
	 * @return String, 为空则返回空字符串
	 */
    private static String getSourceList(List<HashMap<String, Object>> videoList) {
        if(null == videoList || videoList.size() == 0) {
            return "";
        }
        StringBuffer result = new StringBuffer();
        for(int i = 0; i < videoList.size(); i++) {
            result.append(videoList.get(i).get("source")).append(",");
        }
        if (',' == result.charAt(result.length() - 1)) {
            result.deleteCharAt(result.length() - 1);
        }
        return result.toString();
    }

    /**
	 * 外网详情及外网站点源中使用该方法。筛选出参与默认排序的video列表
	 * @param videoList List<HashMap<String, Object>>, 全部数据
	 * @return List<HashMap<String, Object>>, 参与默认排序的video列表，该列表包含所有上非tv平台的数据。无数据则返回空
	 */
	private static List<HashMap<String, Object>> getDefaultVideoList(List<HashMap<String, Object>> videoList) {
	    if(null == videoList || videoList.size() == 0) {
	        return null;
	    }
	    List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
	    for(HashMap<String, Object> video : videoList) {
	        String source = null==video.get("source") ? "" : video.get("source").toString();
	        String is_cibn_checked = null==video.get("is_cibn_checked") ? "" : video.get("is_cibn_checked").toString();
	        // TODO 等待芒果源刷新播控后放开
//	        String video_push_flag = null==video.get("video_push_flag") ? "" : video.get("video_push_flag").toString();
//	        String video_push_flag_untv = video_push_flag.replace(Constants.PUSHFLAG_TV, "").replace(",", "");
	        if("0".equals(is_cibn_checked) || Constants.WEBSITE_LETV.equals(source)) {
//	        if("0".equals(is_cibn_checked) || Constants.WEBSITE_LETV.equals(source) || (Constants.WEBSITE_IMGO.equals(source) && !StringUtils.isBlank(video_push_flag_untv))) {
	            result.add(video);
	        }
	    }
	    if(result.size() == 0) {
	        result = null;
	    }
        return result;
    }

	/**
	 * 外网详情及外网站点源中使用该方法。筛选出参与tv排序的video列表
	 * @param videoList List<HashMap<String, Object>>, 全部数据
	 * @return List<HashMap<String, Object>>, 参与tv排序的video列表，该列表包含所有上tv平台的数据。无数据则返回空
	 */
	private static List<HashMap<String, Object>> getTvVideoList(List<HashMap<String, Object>> videoList) {
	    if(null == videoList || videoList.size() == 0) {
	        return null;
	    }
	    List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
	    for(HashMap<String, Object> video : videoList) {
	        String is_cibn_checked = null==video.get("is_cibn_checked") ? "" : video.get("is_cibn_checked").toString();
	        String video_push_flag = null==video.get("video_push_flag") ? "" : video.get("video_push_flag").toString();
	        if("1".equals(is_cibn_checked) && video_push_flag.indexOf(Constants.PUSHFLAG_TV) >= 0) {
	            result.add(video);
	        }
	    }
	    if(result.size() == 0) {
	        result = null;
	    }
	    return result;
	}

    /**
	 * 获得非默认站点源详情
	 * @param connectionFromLeso Connection, 作品库数据库连接
	 * @param source String, 待请求站点源
	 * @param albumId Long, 请求专辑
	 * @return Map<String, Object>, 站点源信息
	 * @throws SQLException
	 */
    public static Map<String, Object> getVideoSourceByAlbumId(Connection connectionFromLeso, String source, Long albumId) throws SQLException {
        String sql = ConfigFactory.getString("leso.getSourceLesoDataById") + " AND source= ?";
        PreparedStatement preparedStatement = connectionFromLeso.prepareStatement(sql);
        preparedStatement.setLong(1, albumId);
        preparedStatement.setString(2, source);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        Map<String, Object> record = new HashMap<String, Object>();
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                String colName = resultSetMetaData.getColumnLabel(i);
                record.put(colName, resultSet.getObject(i));
            }
        }
        close(resultSet);
        close(preparedStatement);
        record.put("leso_def_web_set", -1);
        record.put("if_default_source", 0);
        return record;
    }

	/**
	 * return letv_original_id by cpId
	 * @param conn Connection, connection from leso
	 * @param cpId Object
	 * @return String, letv_original_id,return null if there is no data or catch errors
	 */
	private static String getLOIDByCpId(Connection conn, Object cpId) {
        if(null == conn || null == cpId) {
            return null;
        }
        String result = null;
        ResultSet rst = null;
        PreparedStatement pstmt = null;
        try {
            String selSql = ConfigFactory.getString("leso.getLoidByCpId");
            pstmt = conn.prepareStatement(selSql);
            pstmt.setString(1, cpId.toString());
            rst = pstmt.executeQuery();
            while (rst.next()) {
                result = rst.getString(1);
                break;
            }
        } catch (Exception e) {
            log.error("get publisher name error,cp_id={}", cpId, e);
        } finally {
            close(rst);
            close(pstmt);
        }
        return result;
    }

    /**
	 * 设置非tv索引中临时的排序值
	 * 乐视权值:-10000,其他权值:在站点源排序表中的序号
	 * 免费权值:-1000,付费权值:0
	 * @param conn 
	 * @param temp
     * @param aid
	 */
    private static void setTempDefSort(Connection conn, HashMap<String, Object> temp, Long aid) {
         int tempDefSort = 0;
        try {
            String albumDomain = getAlbumDomain(conn, aid);
            List<HashMap<String, Object>> albumDomainList = null;
            if (albumDomain != null && albumDomain.length() > 0) {
                albumDomainList = new ArrayList<HashMap<String, Object>>();
                StringTokenizer st = new StringTokenizer(albumDomain, ",");
                while (st.hasMoreTokens()) {
                    String nowDomain = "";
                    int nowId = Integer.parseInt(st.nextToken());
                    HashMap<String, Object> tempDomainHash = new HashMap<String, Object>();
                    tempDomainHash.put("id", nowId);
                    for(int i = 0; i < SOURCE_SORT.size(); i++) {
                        if(((int)SOURCE_SORT.get(i).get("id")) == nowId) {
                            nowDomain = SOURCE_SORT.get(i).get("domain").toString();
                            break;
                        }
                    }
                    tempDomainHash.put("domain", nowDomain);
                    albumDomainList.add(tempDomainHash);
                }
                albumDomainList.addAll(SOURCE_SORT);
            } else {
                albumDomainList = SOURCE_SORT;
            }
            if (Integer.parseInt(temp.get("is_pay").toString()) == -1) {
                tempDefSort = tempDefSort - 1000;
            }
            if(Constants.WEBSITE_LETV.equals(temp.get("source").toString())) {
                tempDefSort = tempDefSort - 10000;
            } else {
                for(int i = 0; i < albumDomainList.size(); i++) {
                    String sortDomain = albumDomainList.get(i).get("domain").toString();
                    if(sortDomain.equals(temp.get("source").toString())) {
                        tempDefSort = tempDefSort + i;
                        break;
                    }
                } 
            }
        } catch(Exception e) {
            tempDefSort = 10000;
            if (temp.get("vid") != null && (!"".equals(temp.get("vid").toString()))) {
                log.error("def sort fail,vid=" + temp.get("vid"), e);
            } else {
                log.error("def sort fail, error to get vid", e);
            }
        } finally {
            temp.put("tempDefSort", tempDefSort);
        }
    }

    /**
     * 设置tv索引中的临时排序值
     * @param video HashMap<String, Object>
     */
    private static void setTempTvSort(HashMap<String, Object> video) {
        int tempTvSort = 0;
        String source = null==video.get("source") ? "" : video.get("source").toString();
        if(Constants.WEBSITE_LETV.equals(source)) {
            tempTvSort = tempTvSort - 10000;
        } else {
            for(int i = 0; i < Constants.TV_LICENSE_SOURCE_LIST.size(); i++) {
                if(Constants.TV_LICENSE_SOURCE_LIST.get(i).equals(source)) {
                    tempTvSort = tempTvSort + i;
                    break;
                }
            }
        }
        video.put("tempTvSort", tempTvSort);
   }

    /**
     * 得到传入专辑特有的站点源排序
     * @param conn
     * @param aid
     * @return
     */
    private static String getAlbumDomain(Connection conn, Long aid) throws SQLException {
        String selSql = "SELECT top_source FROM album WHERE id=?";

        PreparedStatement pstmt = conn.prepareStatement(selSql);
        pstmt.setLong(1, aid);
        ResultSet rst = pstmt.executeQuery();

        String topVideo = null;
        if(rst.next()) {
            topVideo = rst.getString(1); 
        }

        close(rst);
        close(pstmt);

        return topVideo;
    }

    public static String getVideoIdsByPid(Connection conn, String pid) throws SQLException {
		String selSql = ConfigFactory.getString("video.getVideoIdsByPid");

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setString(1, pid);
		ResultSet rst = pstmt.executeQuery();

		rst.next();
		String vids = rst.getString(1);

		close(rst);
		close(pstmt);

		return vids;
	}

	public static String getDocumentaryIdsByPid(Connection conn, String pid) throws SQLException {
		String selSql = ConfigFactory.getString("video.getDocumentaryIdsByPid");

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setString(1, pid);
		ResultSet rst = pstmt.executeQuery();

		rst.next();
		String vids = rst.getString(1);

		close(rst);
		close(pstmt);

		return vids;
	}

	public static String getMusicIdsByPid(Connection conn, String pid, String type) throws SQLException {
		String selSql = ConfigFactory.getString("video.get" + type + "ByPid");

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setString(1, pid);
		ResultSet rst = pstmt.executeQuery();

		rst.next();
		String vids = rst.getString(1);

		close(rst);
		close(pstmt);

		return vids;
	}

	/*public static int getNowEpisodeByPid(Connection conn, String pid) throws SQLException {
		String selSql = ConfigFactory.getString("video.getNowEpisodeByPid");

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setString(1, pid);
		ResultSet rst = pstmt.executeQuery();

		rst.next();
		int nowEpisode = rst.getInt(1);

		close(rst);
		close(pstmt);

		return nowEpisode;
	}*/

	public static ArrayList<Map<String, Object>> getProcessSmallWebsiteData(Connection conn, String table) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = "SELECT * FROM " + table + " WHERE site_type ='2'";

		PreparedStatement pstmt = conn.prepareStatement(selSql);
//		pstmt.setInt(1, begin);
//		pstmt.setInt(2, end);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			records.add(record);
		}

		close(rst);
		close(pstmt);

		return records;
	}

	public static ArrayList<HashMap<String, Object>> getProcessShortVideoData(Connection conn, long aid) throws SQLException {
        ArrayList<HashMap<String, Object>> records = new ArrayList<HashMap<String, Object>>();
        String selSql = "SELECT source,is_pay,id,push_flag AS video_push_flag,isCIBNChecked AS is_cibn_checked FROM video WHERE video_type is not null and state=1 and album_id = ?";
        
        PreparedStatement pstmt = conn.prepareStatement(selSql);
        pstmt.setLong(1, aid);
        ResultSet rst = pstmt.executeQuery();
        
        ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();
        
        while (rst.next()) {
            HashMap<String, Object> record = new HashMap<String, Object>();
            
               for (int i = 1; i <= colCount; i++) {
                String colName = rsmd.getColumnLabel(i);
                 record.put(colName, rst.getObject(i));
            }
            records.add(record);
        }
        
        close(rst);
        close(pstmt);
        
        return records;
	}
	
	public static ArrayList<Map<String, Object>> getProcessNewLesoWebsiteData(Connection conn, int begin, int end) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = ConfigFactory.getString("leso.SQL4");

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setInt(1, begin);
		pstmt.setInt(2, end);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();
			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			Long album_id = Long.parseLong(record.get("leso_aid").toString());
			List<HashMap<String, Object>> videoList = getProcessShortVideoData(conn, album_id);
	        List<HashMap<String, Object>> defaultVideoList = SQLUtil.getDefaultVideoList(videoList);
	        List<HashMap<String, Object>> tvVideoList = SQLUtil.getTvVideoList(videoList);
	        SQLUtil.sortVideo(conn, defaultVideoList, tvVideoList, album_id);
			int defaultSort = SQLUtil.getWebsiteSort(record, defaultVideoList);
			int tvSort = SQLUtil.getWebsiteSort(record, tvVideoList);
			record.put("top_hot", defaultSort);
			record.put("leso_def_web_set", defaultSort);
			record.put("top_hot_tv", tvSort);
			record.put("leso_def_web_set_tv", tvSort);
			records.add(record);
		}
		close(rst);
		close(pstmt);
		return records;
	}

    private static int getWebsiteSort(HashMap<String, Object> video, List<HashMap<String, Object>> videoList) {
        if(null == videoList || videoList.size() == 0) {
            return 10000;
        }
        int result = 10000;
        for(int i = 0; i < videoList.size(); i++) {
            if(videoList.get(i).get("id").toString().equals(video.get("id").toString())) {
                result = i;
                break;
            }
        }
        return result;
    }	

	/**
	 * get cp info between a id range
	 * @param connectionFromLeso Connection, connection from database leso
	 * @param beginId int, not include this id when select from data base
	 * @param endId int, include this id when select from data base
	 * @return List<CpInfoBean>
     * @throws Exception
	 */
    public static List<CpInfoBean> getCpInfoRange(Connection connectionFromLeso, int beginId, int endId) throws Exception {
        if(null == connectionFromLeso || beginId >= endId) {
            return null;
        }
        String sql = ConfigFactory.getString("leso.getCpInfoRange");
        PreparedStatement preparedStatement = connectionFromLeso.prepareStatement(sql);
        preparedStatement.setInt(1, beginId);
        preparedStatement.setInt(2, endId);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<CpInfoBean> result = new ArrayList<CpInfoBean>();
        while (resultSet.next()) {
            CpInfoBean cpInfoBean = new CpInfoBean();
            cpInfoBean.setId(resultSet.getInt("id"));
            cpInfoBean.setLetv_original_id(resultSet.getString("letv_original_id"));
            cpInfoBean.setUrl(resultSet.getString("url"));
            cpInfoBean.setSource(resultSet.getString("source"));
            cpInfoBean.setName(resultSet.getString("name"));
            cpInfoBean.setDesc(resultSet.getString("desc"));
            cpInfoBean.setLocation(resultSet.getString("location"));

            if(null != resultSet.getString("poster")) {
                List<ThumbnailBean> thumbnail_list = new ArrayList<ThumbnailBean>();
                ThumbnailBean thumbnail = new ThumbnailBean();
                thumbnail.setUrl(resultSet.getString("poster"));
                thumbnail_list.add(thumbnail);
                cpInfoBean.setThumbnail_list(thumbnail_list);
            }

            cpInfoBean.setJoin_time(null == resultSet.getTimestamp("join_time") ? null : resultSet.getTimestamp("join_time").getTime());
            cpInfoBean.setComment_count(null == resultSet.getObject("comment_count") ? null : new Long(resultSet.getLong("comment_count")).intValue());
            cpInfoBean.setPlay_count(null == resultSet.getObject("play_count") ? null : resultSet.getLong("play_count"));
            cpInfoBean.setFans_count(null == resultSet.getObject("fans_count") ? null : resultSet.getLong("fans_count"));
            cpInfoBean.setVideo_count(null == resultSet.getObject("video_count") ? null : new Long(resultSet.getLong("video_count")).intValue());

            result.add(cpInfoBean);
        }
        close(resultSet);
        close(preparedStatement);
        return result;
    }

    public static ArrayList<Map<String, Object>> getProcessData(Connection conn, String table, int begin, int end) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = "SELECT * FROM " + table + " WHERE ID >= ? AND ID < ?";
		if (Constants.INDEX_LESO.equals(table)) {
			selSql = selSql + " and playurls !='' ";
		}
		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setInt(1, begin);
		pstmt.setInt(2, end);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			records.add(record);
		}

		close(rst);
		close(pstmt);

		return records;
	}

	public static ArrayList<Map<String, Object>> getProcessDataLimit(Connection conn, String table, int start, int limit) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = "SELECT * FROM " + table + " LIMIT ?, ?";

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setInt(1, start);
		pstmt.setInt(2, limit);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			records.add(record);
		}

		close(rst);
		close(pstmt);

		return records;
	}

	public static HashMap<String, Object> getIndexFieldName(Connection conn, String type) throws SQLException {
		HashMap<String, Object> fieldNameMap = new HashMap<String, Object>();
		String selSql = ConfigFactory.getString("common.getIndexFieldName");

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setString(1, type);
		ResultSet rst = pstmt.executeQuery();

		while (rst.next()) {
			fieldNameMap.put(String.valueOf(rst.getObject(2)), rst.getObject(1));
		}

		close(rst);
		close(pstmt);

		return fieldNameMap;
	}

	public static ArrayList<Map<String, Object>> getCMSIMGData(Connection conn) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = "select cdn_path,name,keyword from cms_img where type = 1 order by img_order desc";

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			records.add(record);
		}

		close(rst);
		close(pstmt);

		return records;
	}

    /**
     * 设置本次离线计算的站点源排序
     */
    public static void setSourceSort(Connection conn) throws SQLException {
        List<HashMap<String, Object>> temp = new ArrayList<HashMap<String, Object>>();
        String selSql = "SELECT id,domain FROM base_site ORDER BY site_sort";
        PreparedStatement pstmt = conn.prepareStatement(selSql);
        ResultSet rst = pstmt.executeQuery();
        while (rst.next()) {
            int id = rst.getInt(1);
            String domain = rst.getString(2);
            HashMap<String, Object> tempHash = new HashMap<String, Object>();
            tempHash.put("id", id);
            tempHash.put("domain", domain);
            temp.add(tempHash);
        }
        close(rst);
        close(pstmt);
        StringBuffer logSource = new StringBuffer("");
        if(temp != null && temp.size() > 0) {
            for(int i = 0; i < temp.size(); i++) {
                logSource.append(temp.get(i).get("domain") + ",");
            }
            log.info("get sort source success:" + logSource.toString());
            SQLUtil.SOURCE_SORT = temp;
        } else {
            log.info("get sort source fail,use def sort");
        }
    }

    /**
     * set source mapping form leso category to vrs category
     * @param connectionFromLeso Connection, connection from leso
     * @throws SQLException
     */
    public static void setCagegoryMapping(Connection connectionFromLeso) throws SQLException {
        String sql = ConfigFactory.getString("common.getCagegoryMapping");
        PreparedStatement preparedStatement = connectionFromLeso.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            int letv_type_id = resultSet.getInt(2);
            String name = resultSet.getString(3);
            Mappings.LESO_ID_MAP.put(new Integer(id).toString(), new Integer(letv_type_id).toString());
            Mappings.CATEGORY_LESO.put(new Integer(id).toString(), name);
        }
        close(resultSet);
        close(preparedStatement);
    }

    public static ArrayList<Map<String, Object>> getMQData(Connection conn) throws SQLException {
		ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
		String selSql = ConfigFactory.getString("common.getMQData");
		int minute = ConfigFactory.getInt("index.incremental.updateLimit");
		int mqSize = ConfigFactory.getInt("index.incremental.updateSize");

		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = new Date();
		long time = date.getTime() - minute * 60 * 1000;
		String minuteBefore = format.format(new Date(time));

		PreparedStatement pstmt = conn.prepareStatement(selSql);
		pstmt.setString(1, minuteBefore);
		pstmt.setInt(2, mqSize);
		ResultSet rst = pstmt.executeQuery();

		ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();

		while (rst.next()) {
			HashMap<String, Object> record = new HashMap<String, Object>();

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnLabel(i);
				record.put(colName, rst.getObject(i));
			}
			records.add(record);
		}

		close(rst);
		close(pstmt);

		return records;
	}

	public static void insertMQData(Connection conn, String record) throws SQLException {
		String insSql = ConfigFactory.getString("common.insertMQData") + " (" + record + ")";

		Statement stmt = conn.createStatement();
		stmt.execute(insSql);
		log.info(insSql);
		close(stmt);
	}

	public static void deleteMQData(Connection conn) throws SQLException {
		String delSql = ConfigFactory.getString("common.deleteMQData");
		int day = ConfigFactory.getInt("index.incremental.deleteLimit");

		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = new Date();
		long time = date.getTime() - day * 24 * 60 * 60 * 1000;
		String dayBefore = format.format(new Date(time));

		PreparedStatement pstmt = conn.prepareStatement(delSql);
		pstmt.setString(1, dayBefore);
		pstmt.execute();

		close(pstmt);
	}

	public static void updateMQFlagById(Connection conn, int id, int flag) throws SQLException {
		String updSql = ConfigFactory.getString("common.updateMQFlagById");

		PreparedStatement pstmt = conn.prepareStatement(updSql);
		pstmt.setInt(1, flag);
		pstmt.setString(2, DateUtil.getTimeNow());
		pstmt.setInt(3, id);
		pstmt.execute();

		close(pstmt);
	}

	public static void insertSourceData(Connection conn, String type, ArrayList<String> records) throws SQLException {
		log.debug("begin insert " + type + " data!");
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		int counter =0;
		String sql = ConfigFactory.getString(type + ".insert" + StringUtil.toFirstLetterUpperCase(type) + "Data");
		String apd = sql.split("\\,")[1];
		apd = " ON DUPLICATE KEY UPDATE " + apd +"=VALUES("+apd+")";
		try{
			for (int i = 0; i < records.size(); i++) {
				String insSql =  sql + " (" + records.get(i) + ")";
				stmt.addBatch(insSql + apd);
				counter++;
			}
			stmt.executeBatch();
		}catch(Exception e){
			log.error("Save record error", e);
		}finally{
			conn.commit();
			close(stmt);
			log.debug("insert " + type + " data "+ counter +" rows!");
		}
	}

	public static void insertSingleSourceData(Connection conn, String type, String record) throws SQLException {
		String insSql = ConfigFactory.getString(type + ".insert" + StringUtil.toFirstLetterUpperCase(type) + "Data");
        String apd = insSql.split("\\,")[1];
        apd = " ON DUPLICATE KEY UPDATE " + apd +"=VALUES("+apd+")";
		Statement stmt = conn.createStatement();
		String sql =  insSql + " (" + record + ")";
		stmt.execute(sql  + apd );
		log.info("insert " + type + " data success!");
		close(stmt);
	}

	public static void insertIndexData(Connection conn, String type, ArrayList<String> records) throws SQLException {
		log.debug("begin insert index " + type + " data!");
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		for (int i = 0; i < records.size(); i++) {
		    String sql = type + ".insertIndex" + StringUtil.toFirstLetterUpperCase(type) + "Data";
		    //sql = sql.replace("index_"+type, "index_"+type+"_n");
			String insSql = ConfigFactory.getString(sql) + " (" + records.get(i) + ")";
			stmt.addBatch(insSql);
		}
		stmt.executeBatch();
		conn.commit();
		close(stmt);
		log.debug("insert index " + type + " data finished!");
	}

    public static void insertSingleSourceDataByP(Connection conn, String type,
            ArrayList<Object> values) throws SQLException {
        ArrayList<ArrayList<Object>> records = new ArrayList<ArrayList<Object>>();
        records.add(values);
        insertSourceDataByP(conn,type,records);
    }

	public static void insertSourceDataByP(Connection conn, String type, ArrayList<ArrayList<Object>> records) throws SQLException {
		log.debug("begin insert index " + type + " data!");

		conn.setAutoCommit(false);
		String preparedSql = ganerateSQL(type,records.get(0).size());
		log.info(preparedSql);
		PreparedStatement stmt = conn.prepareStatement(preparedSql);

		for (int i = 0; i < records.size(); i++) {
			setValues(stmt,records.get(i));
			stmt.addBatch();
		}
		stmt.executeBatch();
		conn.commit();

		close(stmt);
		log.debug("insert index " + type + " data finished!");
	}

	private static String ganerateSQL(String type, int size) {
		StringBuilder insSql = new StringBuilder(ConfigFactory.getString(type + ".insert" + StringUtil.toFirstLetterUpperCase(type) + "Data"));
		insSql.append(" (");
		for (int i=0;i<size;i++) {
			insSql.append("?,");
		}
		insSql.deleteCharAt(insSql.lastIndexOf(","));
		return insSql.append(") ").toString();
	}

    private static String sqlInFormat(String sql, int size) {
        if (size <= 0) {
            return sql;
        }
        StringBuilder inSql = new StringBuilder();
        for (int i = 0; i < size; i++) {
            inSql.append("?,");
        }
        return String.format(sql, inSql.toString());
    }

	public static void insertSingleIndexData(Connection conn, String type, String record) throws SQLException {
		String insSql = ConfigFactory.getString(type + ".insertIndex" + StringUtil.toFirstLetterUpperCase(type) + "Data") + " (" + record + ")";

		Statement stmt = conn.createStatement();
		stmt.execute(insSql);

		close(stmt);
	}

	public static void setValues(PreparedStatement stmt, ArrayList<Object> arrayList) throws SQLException {
	    for (int i = 0; i < arrayList.size(); i++) {
	        stmt.setObject(i + 1, arrayList.get(i));
	    }
	}

	public static void deleteDataById(Connection conn, String table, Long id) throws SQLException {
		String delSql = "DELETE FROM " + table + " WHERE ID=" + id;

		Statement stmt = conn.createStatement();
		stmt.execute(delSql);

		close(stmt);
	}

	public static void close(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		} catch (SQLException e) {
			log.error("close statement error!", e);
		}
	}

	public static void close(PreparedStatement pstmt) {
		try {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			log.error("close prepared statement error!", e);
		}
	}

	public static void close(ResultSet rst) {
		try {
			if (rst != null) {
				rst.close();
				rst = null;
			}
		} catch (SQLException e) {
			log.error("close result set error!", e);
		}
	}

    public static boolean close(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
            return true;
        } catch (Exception e) {
            log.error("close database connection error!", e);
            return false;
        }
    }

    public static boolean close(Connection... connections) {

        boolean ret = true;

        for(Connection conn : connections) {
            ret = close(conn) && ret;
        }
        return ret;
    }

    /**
     * 根据cp_info_id获取
     * @param conn
     * @param cpId
     * @return
     */
    public static String getPublisherName(Connection conn, Object cpId) {
        String publisher = null;
        ResultSet rst = null;
        PreparedStatement pstmt = null;
        try {
            String selSql = ConfigFactory.getString("leso.getUserName");
            pstmt = conn.prepareStatement(selSql);
            pstmt.setString(1, cpId.toString());
            rst = pstmt.executeQuery();
            while (rst.next()) {
                publisher = rst.getString(1);
                break;
            }
        } catch (Exception e) {
            log.error("get publisher name error,cp_id={}", cpId, e);
        } finally {
            close(rst);
            close(pstmt);
        }
        return publisher;
    }

    public static ArrayList<Map<String, Object>> getLesoAlbumVideoSourceData(Connection conn, String sqlPath,
            String vid, List<String> params) throws SQLException {
        ArrayList<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
        String selSql = ConfigFactory.getString(sqlPath);
        if (selSql == null || params == null) {
            return null;
        }
        selSql = sqlInFormat(selSql, params.size());
        PreparedStatement pstmt = conn.prepareStatement(selSql);
        pstmt.setObject(1, vid);
        int size = params.size() + 1;
        for (int i = 2; i <= size; i++) {
            pstmt.setObject(i, params.get(i - 2));
        }
        ResultSet rst = pstmt.executeQuery();
        ResultSetMetaData rsmd = rst.getMetaData();
        int colCount = rsmd.getColumnCount();
        while (rst.next()) {
            HashMap<String, Object> record = new HashMap<String, Object>();

            for (int i = 1; i <= colCount; i++) {
                String colName = rsmd.getColumnLabel(i);
                record.put(colName, rst.getObject(i));
            }
            records.add(record);
        }
        close(rst);
        close(pstmt);
        return records;
    }
    
    public static List<SourceLogoBean> getAllSourceLogos(Connection conn) throws SQLException {
		String selSql = ConfigFactory.getString("sourceLogo.getAllSourceLogos");

		Statement stmt = conn.createStatement();
		ResultSet rst = stmt.executeQuery(selSql);
		List<SourceLogoBean> resultList = new ArrayList<SourceLogoBean>();

		while (rst.next()) {
			SourceLogoBean bean = new SourceLogoBean();
			String source_name = String.valueOf(rst.getObject(1));
			bean.setSourceName(source_name);
			String logoUrl = String.valueOf(rst.getObject(2));
			bean.setLogoUrl(logoUrl);
			resultList.add(bean);
		}
		close(rst);
		close(stmt);
		return resultList;
	}

    /**
     * get album id list by cp letvOriginalId from database
     * @param connectionFromLocal Connection, connection from local
     * @param cpLetvOriginalId String
     * @return List<String>, return null while have errors or no data
     */
    public static List<String> getAlbumIdListByCpOId(Connection connectionFromLocal, String cpLetvOriginalId) throws Exception {
        if(null == connectionFromLocal || StringUtils.isBlank(cpLetvOriginalId)) {
            return null;
        }
        String sql = ConfigFactory.getString("leso.getAlbumIdListByCpOId");
        PreparedStatement preparedStatement = connectionFromLocal.prepareStatement(sql);
        preparedStatement.setString(1, cpLetvOriginalId);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<String> result = new ArrayList<String>();
        while (resultSet.next()) {
            long albumId = resultSet.getLong(1);
            result.add(albumId + "");
        }
        close(resultSet);
        close(preparedStatement);
        if(null != result && result.size() == 0) {
            result = null;
        }
        return result;
    }

    /**
     * 获得非默认站点源sorce列表
     * @param connectionFromLocal Connection, 与本地数据库的连接
     * @param albumId String, 外网专辑id
     * @return List<String>
     * @throws Exception
     */
    public static List<String> getOtherDetailSourceList(Connection connectionFromLocal, String albumId) throws Exception {
        if(null == connectionFromLocal || StringUtils.isBlank(albumId)) {
            return null;
        }
        String sql = ConfigFactory.getString("leso.getOtherDetailSourceList");
        PreparedStatement preparedStatement = connectionFromLocal.prepareStatement(sql);
        preparedStatement.setString(1, albumId);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<String> result = new ArrayList<String>();
        while (resultSet.next()) {
            String source = resultSet.getString(1);
            result.add(source);
        }
        close(resultSet);
        close(preparedStatement);
        if(null != result && result.size() == 0) {
            result = null;
        }
        return result;
    }

    /**
     * get cp id by album id limit 1(all cpid in one album is the same)
     * @param connectionFromLocal Connection, connection from local
     * @param albumId String
     * @return Integer
     */
    public static Integer getCpIdByAlbumId(Connection connectionFromLocal, String albumId) throws Exception {
        if(null == connectionFromLocal || StringUtils.isBlank(albumId)) {
            return null;
        }
        String sql = ConfigFactory.getString("leso.getCpIdByAlbumId");
        PreparedStatement preparedStatement = connectionFromLocal.prepareStatement(sql);
        preparedStatement.setString(1, albumId);
        ResultSet resultSet = preparedStatement.executeQuery();
        Integer back = null;
        if(resultSet.next()) {
            back = resultSet.getInt(1);
        }
        close(resultSet);
        close(preparedStatement);
        return back;
    }

    /**
     * get h5 blocked city level by album_id
     * @param connectionFromLeso Connection, connection from leso
     * @param albumId String, album id
     * @return Object, h5 blocked city level matched album_id which is given
     */
    public static Object getH5BlockByAlbumId(Connection connectionFromLeso, String albumId) throws SQLException {
        if(null == connectionFromLeso || StringUtils.isBlank(albumId)) {
            return null;
        }
        String sql = ConfigFactory.getString("leso.getH5ByAlbumId");
        PreparedStatement preparedStatement = connectionFromLeso.prepareStatement(sql);
        preparedStatement.setString(1, albumId);
        ResultSet resultSet = preparedStatement.executeQuery();
        Object back = null;
        if(resultSet.next()) {
            back = resultSet.getObject(1);
        }
        close(resultSet);
        close(preparedStatement);
        return back;
    }

    /**
     * 得到cp下单视频列表
     * @param cpLetvOriginalId String, cp全局id
     * @param connectionFromLocal Connection, 离线计算本地连接
     * @return List<Map<String, Object>>, list中的元素为HashMap，每个Map代表一个专辑。为空或出错则返回null
     * @throws SQLException
     */
    public static List<Map<String, Object>> getSvideoByCpOId(Connection connectionFromLocal, String cpLetvOriginalId) throws SQLException {
        if(null == connectionFromLocal || StringUtils.isBlank(cpLetvOriginalId)) {
            return null;
        }
        String sql = ConfigFactory.getString("leso.getSvideoByCpOId");
        PreparedStatement preparedStatement = connectionFromLocal.prepareStatement(sql);
        preparedStatement.setString(1, cpLetvOriginalId);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        while (resultSet.next()) {
            HashMap<String, Object> record = new HashMap<String, Object>();
            for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                record.put(resultSetMetaData.getColumnLabel(i), resultSet.getObject(i));
            }
            if(null != record.get("currentEpisode") && null != record.get("episodes")) {
                try {
                    int currentEpisode = Integer.parseInt(record.get("currentEpisode").toString());
                    int episodes = Integer.parseInt(record.get("episodes").toString());
                    if(currentEpisode < episodes) {
                        record.put("isEnd", "0");
                    } else {
                        record.put("isEnd", "1");
                    }
                } catch(Exception e) {
                    record.put("isEnd", "1");
                }
            } else {
                record.put("isEnd", "1");
            }
            result.add(record);
        }
        close(resultSet);
        close(preparedStatement);
        if(null != result && result.size() == 0) {
            result = null;
        }
        return result;
    }

    /**
     * 返回传入id在index album/video 的个数
     * @param connectionFromLocal Connection
     * @param mqAddType mqAddType
     * @param id long
     * @return int
     * @throws SQLException
     */
    public static int getMmsCountById(Connection connectionFromLocal, Type mqAddType, long id) throws SQLException {
        StringBuffer sqlBuffer = new StringBuffer("SELECT COUNT(1) FROM index_");
        if(Type.ALBUM.equals(mqAddType)) {
            sqlBuffer.append("album");
        } else {
            sqlBuffer.append("video");
        }
        sqlBuffer.append(" WHERE id=").append(id);
        Statement statement = connectionFromLocal.createStatement();
        ResultSet resultSet = statement.executeQuery(sqlBuffer.toString());
        resultSet.next();
        int count = resultSet.getInt(1);
        close(resultSet);
        close(statement);
        return count;
    }

    public static void main(String[] args) {
        ConfigFactory.init("E:\\code\\shouye\\search_java\\offline_task\\trunk\\target\\ROOT\\WEB-INF\\classes\\config.xml");
        String log4jPath = "E:\\code\\shouye\\search_java\\offline_task\\trunk\\target\\ROOT\\WEB-INF\\classes\\log4j.properties";
        PropertyConfigurator.configure(log4jPath);
        ConnectionManager cm = null;
        try {
            cm = ConnectionManager.getInstance();
        } catch (Exception e) {  
            log.error("connection get instance error!", e);
        }
         Connection conn = cm.getConnectionFromLeso();
        try {
            setSourceSort(conn);
            HashMap<String, Object> video = getSourceDataById(conn, "leso", 10319L);
            System.out.println(JSON.toJSONString(video));
//            int vid = 2566546;
//            Map<String, Object> video = getProcessNewLesoWebsiteData(conn, vid, vid + 1).get(0);
//            System.out.println(JSON.toJSONString(video));
//            System.out.println("leso_def_web_set=" + video.get("leso_def_web_set"));
//            System.out.println("top_hot=" + video.get("top_hot"));
//            System.out.println("leso_def_web_set_tv=" + video.get("leso_def_web_set_tv"));
//            System.out.println("top_hot_tv=" + video.get("top_hot_tv"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class SortByDefSort implements Comparator<HashMap<String, Object>> {
    @Override
    public int compare(HashMap<String, Object> h1, HashMap<String, Object> h2) {
        return Integer.parseInt(h1.get("tempDefSort").toString()) - Integer.parseInt(h2.get("tempDefSort").toString());
    }
}

class SortByTvSort implements Comparator<HashMap<String, Object>> {
    @Override
    public int compare(HashMap<String, Object> h1, HashMap<String, Object> h2) {
        return Integer.parseInt(h1.get("tempTvSort").toString()) - Integer.parseInt(h2.get("tempTvSort").toString());
    }
}
